using System;
using System.Collections;
using System.Data;
using System.Threading;
using System.Web.UI;
using System.Web.UI.WebControls;
using PHSRAG.Insight21.Agreements;
using PHSRAG.Insight21.Common.Pages;
using PHSRAG.Insight21.Context;
using PHSRAG.Insight21.InsightWebControls;
using PHSRAG.Insight21.Policy;
using PHSRAG.Insight21.SearchContext;
using PHSRAG.Security.Enumerations;
using PHSRAG.Utility;
using PHSRAG.Security;
using System.Xml;
using System.IO;
using System.Text;
using PHSRAG.WebControls;
using Constants = PHSRAG.Insight21.InsightUtilities.Constants;
using Helper = PHSRAG.WebControls.Helper;

namespace PHSRAG.Insight21.Agreements
{
    /// <summary>
    /// The StimulusReporting object is the class supporting the like-named page. It displays a report view of all
    /// the 3 major components required to submit to the federal government in every quarter of the year.
    /// </summary>    
    public partial class StimulusReporting : NoCachePage
    {
        #region Instance Variables (Private)
                
        private ICachePolicy cachePolicy;
        private ICachePolicy pageCachePolicy;
        
        private int GridType;  
        private int loggedInUserID;  
        private int intAccessLevel;
        private XmlDocument doc = null;
        private DataTable dtGrid;

        #endregion

        #region Constants

        private const string PageCacheKey = "pageCacheKey_F8B60754-BA12-4526-8633-33CA16B0452E";
        private const string CurrentPageIndexKey = "StimulusFundingPageIndexKey_49D76E35-8E27-4ef7-BF57-4AC5C5BA9E97";
        private const string SearchCacheKey = "ArraSearchCacheKey_7357C24C-19D3-4d5e-8807-5133DFDB6C6F";
        private const string ToggleKey = "ArraToggleKey_57FA6CB9-D7D0-4431-A2E0-7D193307F2BF";
        private const string SortKey = "ArraSortKey_D4442682-6248-4db4-9C34-202150D730F2";
        private const string SortDirection = "ArraSortDirection_D3BD203C-93CD-4b99-BE14-1AA6E3158F03";  
        private const string PageName = "Stimulus Funding";

        #endregion

        #region Private Properties

        /// <summary>
        /// Get/Set the current page within the datagrid for display. This information persists across page loads.
        /// </summary>
        private int CurrentPageIndex
        {
            get
            {
                Hashtable h = pageCachePolicy.GetCachedObject(Keys.StimulusFundingPageKey) as Hashtable;
                return (h == null || h[CurrentPageIndexKey] == null) ? 0 : (int)h[CurrentPageIndexKey];
            }
            set
            {
                Hashtable h = pageCachePolicy.GetCachedObject(Keys.StimulusFundingPageKey) as Hashtable ?? new Hashtable();
                h[CurrentPageIndexKey] = value;
                
                pageCachePolicy.Cache(Keys.StimulusFundingPageKey, h);
            }
        }

        /// <summary>
        /// Get/Set the current page within the datagrid for display. This information persists across page loads.
        /// </summary>
        private int Toggle
        {
            get
            {
                Hashtable h = pageCachePolicy.GetCachedObject(Keys.StimulusFundingPageKey) as Hashtable;
                return (h == null || h[ToggleKey] == null) ? 0 : (int)h[ToggleKey];
            }
            set
            {
                Hashtable h = pageCachePolicy.GetCachedObject(Keys.StimulusFundingPageKey) as Hashtable ?? new Hashtable();
                h[ToggleKey] = value;

                pageCachePolicy.Cache(Keys.StimulusFundingPageKey, h);
            }
        }

        /// <summary>
        /// 
        /// </summary>
        private string SortField
        {
            get
            {
                Hashtable h = pageCachePolicy.GetCachedObject(Keys.StimulusFundingPageKey) as Hashtable;
                return (h == null || h[SortKey] == null) ? "" : h[SortKey].ToString();
            }
            set
            {
                Hashtable h = pageCachePolicy.GetCachedObject(Keys.StimulusFundingPageKey) as Hashtable ?? new Hashtable();
                h[SortKey] = value;

                pageCachePolicy.Cache(Keys.StimulusFundingPageKey, h);
            }
        }

        private string SortDir
        {
            get
            {
                Hashtable h = pageCachePolicy.GetCachedObject(Keys.StimulusFundingPageKey) as Hashtable;
                return (h == null || h[SortDirection] == null) ? "" : h[SortDirection].ToString();
            }
            set
            {
                Hashtable h = pageCachePolicy.GetCachedObject(Keys.StimulusFundingPageKey) as Hashtable ?? new Hashtable();
                h[SortDirection] = value;

                pageCachePolicy.Cache(Keys.StimulusFundingPageKey, h);
            }
        }

        #endregion

        #region Event Handlers

        /// <summary>
        /// Handler for the event raised when the FirstPage link in clicked in the Pager control.
        /// Set the current page index to 0, bind the datagrid with data and manage navigation links. To avoid superfluous
        /// data binding activites, no action is taken if the current page index is already 0.
        /// </summary>
        /// <param name="sender">Control which rasied the event</param>
        /// <param name="e">Arguments associated with the event</param>
        protected void OnFirstPage(object sender, CommandEventArgs e)
        {
            try
            {
                CurrentPageIndex = 0;
                LoadDataGrid();                
            }
            catch (Exception ex)
            {
                DisplayExceptionReport(ex);
            }
        }
        
        /// <summary>
        /// Handler for the event raised when the LastPage link in clicked in the Pager control.
        /// Set the current page index to one less than the total page count (index is 0-based), bind the datagrid with
        /// data and manage navigation links. To avoid superfluous data binding activites, no action is taken if the current
        /// page index is already corresponding to the last page.
        /// </summary>
        /// <param name="sender">Control which rasied the event</param>
        /// <param name="e">Arguments associated with the event</param>
        protected void OnLastPage(object sender, CommandEventArgs e)
        {
            try
            {
                CurrentPageIndex = pagerAboveDatagrid.PageCount - 1;
                LoadDataGrid();
            }
            catch (Exception ex)
            {
                DisplayExceptionReport(ex);
            }
        }

        /// <summary>
        /// Handler for the event raised when the NextPage link in clicked in the Pager control.
        /// Increment the current page index, bind the datagrid with data and manage navigation links.
        /// </summary>
        /// <param name="sender">Control which rasied the event</param>
        /// <param name="e">Arguments associated with the event</param>
        protected void OnNextPage(object sender, CommandEventArgs e)
        {
            try
            {
                ++CurrentPageIndex;
                LoadDataGrid();
            }
            catch (Exception ex)
            {
                DisplayExceptionReport(ex);
            }
        }

        /// <summary>
        /// Handler for the event raised when the PreviousPage link in clicked in the Pager control.
        /// Decrement the current page index, bind the datagrid with data and manage navigation links.
        /// </summary>
        /// <param name="sender">Control which rasied the event</param>
        /// <param name="e">Arguments associated with the event</param>
        protected void OnPreviousPage(object sender, CommandEventArgs e)
        {
            try
            {
                --CurrentPageIndex;
                LoadDataGrid();
            }
            catch (Exception ex)
            {
                DisplayExceptionReport(ex);
            }
        }

        protected void dgProposal_ItemDataBound(object sender, DataGridItemEventArgs e)
        {
            if (GridType == 1)
            {
                e.Item.Cells[41].Visible = false;
                e.Item.Cells[42].Visible = false;
            }
            else if (GridType == 2)
            {
                e.Item.Cells[20].Visible = false;
                e.Item.Cells[21].Visible = false;
            }
            else if (GridType == 3)
            {
                e.Item.Cells[9].Visible = false;
                e.Item.Cells[10].Visible = false;
            }
        }

        protected void imgBtnSearchReport_Click(object sender, ImageClickEventArgs e)
        {
            try
            {
                Hashtable hst = GetSearchFromCache();
                hst["PSFundNo"] = txtPSFundNo.Text.Trim();
                hst["AwardNo"] = txtSponsorAwardNo.Text.Trim();
                hst["Sponsor"] = txtSponsor.Text.Trim();
                hst["ProposalNo"] = txtProposalNo.Text.Trim();
                hst["DAFirstName"] = txtDAFirstName.Text.Trim();
                hst["DALastName"] = txtDALastName.Text.Trim();
                hst["PIFirstName"] = txtPIFirstName.Text.Trim();
                hst["PILastName"] = txtPILastName.Text.Trim();
                hst["DocumentID"] = txtDocumentID.Text.Trim();
                hst["ReportPeriodID"] = drpReportPeriod.SelectedValue;
                hst["ReportPeriodName"] = drpReportPeriod.SelectedItem.ToString();
                SetSearchFromCache(hst);

                CurrentPageIndex = 0;
                LoadDataGrid();
            }
            catch (Exception ex)
            {
                annunciator.Title = "Following error(s) occurred during Searching:";
                annunciator.Message = ExceptionReport.GetExceptionInfo(ex, false);
            }
        }

        protected void imgBtnClear_Click(object sender, ImageClickEventArgs e)
        {
            txtPIFirstName.Text = "";
            txtPILastName.Text = "";
            txtSponsor.Text = "";
            txtSponsorAwardNo.Text = "";
            txtProposalNo.Text = "";
            txtPSFundNo.Text = "";
            txtDocumentID.Text = "";
            txtDAFirstName.Text = "";
            txtDALastName.Text = "";
        }

        /// <summary>
        /// Handler for the event raised when the page is being loaded.
        /// Initialize the bread crumb control, set the page size for the data grid, and load the data grid with data.
        /// If on the first page within the data grid, initalize the pager control. Notice that even when in the context
        /// of a non-postback load, the current page index may not be 0 since the page index is restored from the cache.
        /// The data grid is loaded with data on every page load because the data grid is set not to use ViewState.
        /// </summary>
        /// <param name="sender">Control which rasied the event</param>
        /// <param name="e">Arguments associated with the event</param>
        protected void Page_Load(object sender, EventArgs e)  
        {
            try
            {
                annunciator.Message = string.Empty;
                imgBtnReport.Attributes.Add("onclick", "return confirmreport();");
                btnAdvSearch.Attributes.Add("onclick", "return ShowHideSearch();");
                breadCrumb.Add("Stimulus Funding", Settings.GetKeyValue("SubNavigation.Agreements.StimulusReportingURL", ""), 1);

                string QueryString = Request.QueryString["sl"].ToString();
                if (QueryString == "prop")  
                {
                    GridType = 1;
                    pageName.Text = "Proposal Data";
                    breadCrumb.Add("Proposal Data", Settings.GetKeyValue("SubNavigation.Agreements.StimulusReportingURL", ""), 2);
                    downloadBar.SQLReportName = "arra_proposaldata";
                }
                else if (QueryString == "sub")
                {
                    GridType = 2;
                    pageName.Text = "SubRecipient Data";
                    breadCrumb.Add("SubRecipient Data", Settings.GetKeyValue("SubNavigation.Agreements.StimulusReportingURL", ""), 2);
                    downloadBar.SQLReportName = "arra_subrecipientdata";
                }
                else if (QueryString == "vendor")
                {
                    GridType = 3;
                    pageName.Text = "Vendor Data";
                    breadCrumb.Add("Vendor Data", Settings.GetKeyValue("SubNavigation.Agreements.StimulusReportingURL", ""), 2);
                    downloadBar.SQLReportName = "arra_vendordata";
                }
                                
                loggedInUserID = ((UserContext)cachePolicy.GetCachedObject(UserContext.CacheKey)).DomainUserId;
                
                if (!IsPostBack)
                {
                    StimulusFunding sf = new StimulusFunding();
                    DataTable dtAllPeriods = sf.GetAllReportPeriod(DataAccess.GetConnectionString());
                    ListItem li;
                    //drpReportPeriod.Items.Add("");
                    foreach (DataRow dr in dtAllPeriods.Rows)
                    {
                        li = new ListItem(string.Format("{0} - {1}",dr["QuarterStartDate"].ToString(),dr["QuarterEndDate"].ToString()),dr["ID"].ToString()); 
                        drpReportPeriod.Items.Add(li);
                    }

                    DataTable dtPeriod = sf.GetCurrentReportPeriod(DataAccess.GetConnectionString());
                    Session["ReportPeriod"] = Convert.ToInt32(dtPeriod.Rows[0]["ID"]);

                    Hashtable hst = GetSearchFromCache();

                    if (hst.Count > 0)
                    {
                        hst["ReportPeriodID"] = dtPeriod.Rows[0]["ID"].ToString();
                        hst["ReportPeriodName"] = dtPeriod.Rows[0]["QuarterStartDate"].ToString() + " - " + dtPeriod.Rows[0]["QuarterEndDate"].ToString();
                        drpReportPeriod.SelectedValue = dtPeriod.Rows[0]["ID"].ToString();
                        SetSearchFromCache(hst);
                    }                   

                    hst = GetSearchFromCache();
                    if (hst.Count > 0)
                    {
                        txtPSFundNo.Text = hst["PSFundNo"].ToString();
                        txtSponsorAwardNo.Text = hst["AwardNo"].ToString();
                        txtSponsor.Text = hst["Sponsor"].ToString();
                        txtProposalNo.Text = hst["ProposalNo"].ToString();
                        txtDAFirstName.Text = hst["DAFirstName"].ToString();
                        txtDALastName.Text = hst["DALastName"].ToString();
                        txtPIFirstName.Text = hst["PIFirstName"].ToString();
                        txtPILastName.Text = hst["PILastName"].ToString();
                        txtDocumentID.Text = hst["DocumentID"].ToString();
                        //drpReportPeriod.SelectedValue = hst["ReportPeriodID"].ToString();                       
                    }
                    else
                    {
                        hst["PSFundNo"] = "";
                        hst["AwardNo"] = "";
                        hst["PILastName"] = "";
                        hst["PIFirstName"] = "";
                        hst["Sponsor"] = "";
                        hst["ProposalNo"] = "";
                        hst["DAFirstName"] = "";
                        hst["DALastName"] = "";
                        hst["DocumentID"] = "";
                        hst["ReportPeriodID"] = dtPeriod.Rows[0]["ID"].ToString();
                        hst["ReportPeriodName"] = dtPeriod.Rows[0]["QuarterStartDate"].ToString() + " - " + dtPeriod.Rows[0]["QuarterEndDate"].ToString();
                        drpReportPeriod.SelectedValue = dtPeriod.Rows[0]["ID"].ToString(); 
                        SetSearchFromCache(hst);
                    }

                    if ((int)Security.Enumerations.ModuleRoleBit.SetArraIsFinalReport > 0)
                    intAccessLevel = intAccessLevel | ((UserContext)cachePolicy.GetCachedObject(UserContext.CacheKey)).ModuleRoleBitmask;
                    if ((intAccessLevel & (int)Security.Enumerations.ModuleRoleBit.SetArraIsFinalReport) > 0)                      
                    {
                        if (Convert.ToInt32(dtPeriod.Rows[0]["IsFinalReport"]) == 0)
                        {
                            imgBtnReport.Visible = true;
                        }
                        else
                        {
                            imgBtnReport.Visible = false;
                        }
                    }

                    CurrentPageIndex = 0;
                    SortField = "AgrFct.SponsorNumber";
                    SortDir = "ASC";
                    LoadDataGrid();     
                }
            }
            catch (Exception ex)
            {
                annunciator.Title = "Following error(s) occurred during PageLoad:";
                annunciator.Message = ExceptionReport.GetExceptionInfo(ex, false);
            }
            finally
            {
                agreementsSubNavigation.SelectedLink = "Stimulus Funding";
            }
        }

        /// <summary>
        /// Purges the cache and sets the correct search context level before the breadcrumb performs a redirect.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void OnNavigate(object sender, CommandEventArgs e)
        {
            if (((CommandEventArgs)(e.CommandArgument)).CommandName == Convert.ToString((int)SearchCriteria.Level.Summary))
            {
                //set the search context's level to the correct level
                CommonSearchContext searchContext = (CommonSearchContext)cachePolicy.GetCachedObject(CommonSearchContext.CacheKey);
                searchContext.CurrentLevel = SearchCriteria.Level.Summary;
            }
            PurgeCache();
        }

        /// <summary>
        /// Invoked by the download bar when it catches an exception.
        /// </summary>
        /// <param name="sender">Control which raised the event</param>
        /// <param name="e">Argument associated with the event</param>
        protected void OnDownloadFailed(object sender, CommandEventArgs e)
        {
            annunciator.Title = "Following error(s) occurred while downloading";
            annunciator.Message = ExceptionReport.GetExceptionInfo((Exception)e.CommandArgument, false);
        }

        /// <summary>
        /// Purge the cached BO from the cache and remove its key from the cache.
        /// </summary>
        private void PurgeCache()
        {
            System.Diagnostics.Trace.WriteLine("Purging StimulusFunding...");
            ResultsCache.PurgeObject(cachePolicy);
        }

        /// <summary>
        /// Purges the cache before the agreements subnavigation header performs a redirect.
        /// </summary>
        /// <param name="sender">Control which rasied the event.</param>
        /// <param name="e">Arguments associated with the event.</param>
        protected void OnSubNavigate(object sender, CommandEventArgs e)
        {
            PurgeCache();
        }

        /// <summary>
        /// Resets the Agreements subnavigation selected link before the 
        /// header performs a redirect.
        /// </summary>
        /// <param name="sender">Control which rasied the event.</param>
        /// <param name="e">Arguments associated with the event.</param>
        protected void OnHeaderNavigate(object sender, CommandEventArgs e)
        {
            PurgeCache();
            agreementsSubNavigation.Reset();
        }

        #endregion

        #region Private Methods

        /// <summary>
        /// GetProposalData instanciates the StimulusFunding object and returns a resultset.
        /// </summary>
        /// <returns>ProposalData DataSet</returns>
        private DataTable GetProposalData(Hashtable hst, int StartPageIndex, int EndingPageIndex, int PageSize, string SortOrder)
        {
            StimulusFunding sf = new StimulusFunding();
            try
            {
                string PSFundNo = hst["PSFundNo"].ToString();
                string AwardNo = hst["AwardNo"].ToString();
                string PILastName = hst["PILastName"].ToString();
                string PIFirstName = hst["PIFirstName"].ToString();
                string Sponsor = hst["Sponsor"].ToString();
                string ProposalNo = hst["ProposalNo"].ToString();
                string DAFirstName = hst["DAFirstName"].ToString();
                string DALastName = hst["DALastName"].ToString();
                string DocumentID = hst["DocumentID"].ToString();
                int ReportPeriodID = Convert.ToInt32(hst["ReportPeriodID"].ToString());

                dtGrid = sf.GetProposalData(DataAccess.GetConnectionString(), PSFundNo, AwardNo, PILastName, PIFirstName, Sponsor, ProposalNo, DAFirstName, DALastName, DocumentID, ReportPeriodID, StartPageIndex, EndingPageIndex, PageSize, SortOrder, loggedInUserID);
            }
            catch (Exception e)
            {
                throw new Exception(e.Message, e.InnerException);
            }
            return dtGrid;            
        }

        /// <summary>
        /// GetSubRecipientData instanciates the StimulusFunding object and returns a resultset.
        /// </summary>
        /// <returns>ProposalData DataSet</returns>
        private DataTable GetSubRecipientData(Hashtable hst, int StartPageIndex, int EndingPageIndex, int PageSize, string SortOrder)
        {
            StimulusFunding sf = new StimulusFunding();
            try
            {
                string PSFundNo = hst["PSFundNo"].ToString();
                string AwardNo = hst["AwardNo"].ToString();
                string PILastName = hst["PILastName"].ToString();
                string PIFirstName = hst["PIFirstName"].ToString();
                string Sponsor = hst["Sponsor"].ToString();
                string ProposalNo = hst["ProposalNo"].ToString();
                string DAFirstName = hst["DAFirstName"].ToString();
                string DALastName = hst["DALastName"].ToString();
                string DocumentID = hst["DocumentID"].ToString();
                int ReportPeriodID = Convert.ToInt32(hst["ReportPeriodID"].ToString());

                dtGrid = sf.GetSubRecipientData(DataAccess.GetConnectionString(), PSFundNo, AwardNo, PILastName, PIFirstName, Sponsor, ProposalNo, DAFirstName, DALastName, DocumentID, ReportPeriodID, StartPageIndex, EndingPageIndex, PageSize, SortOrder, loggedInUserID);
            }
            catch (Exception e)
            {
                throw new Exception(e.Message, e.InnerException);
            }
            return dtGrid;
        }

        /// <summary>
        /// GetVendorData instanciates the StimulusFunding object and returns a resultset.
        /// </summary>
        /// <returns>ProposalData DataSet</returns>
        private DataTable GetVendorData(Hashtable hst, int StartPageIndex, int EndingPageIndex, int PageSize, string SortOrder)
        {
            StimulusFunding sf = new StimulusFunding();
            try
            {
                string PSFundNo = hst["PSFundNo"].ToString();
                string AwardNo = hst["AwardNo"].ToString();
                string PILastName = hst["PILastName"].ToString();
                string PIFirstName = hst["PIFirstName"].ToString();
                string Sponsor = hst["Sponsor"].ToString();
                string ProposalNo = hst["ProposalNo"].ToString();
                string DAFirstName = hst["DAFirstName"].ToString();
                string DALastName = hst["DALastName"].ToString();
                string DocumentID = hst["DocumentID"].ToString();
                int ReportPeriodID = Convert.ToInt32(hst["ReportPeriodID"].ToString());

                dtGrid = sf.GetVendorData(DataAccess.GetConnectionString(), PSFundNo, AwardNo, PILastName, PIFirstName, Sponsor, ProposalNo, DAFirstName, DALastName, DocumentID, ReportPeriodID, StartPageIndex, EndingPageIndex, PageSize, SortOrder, loggedInUserID);
            }
            catch (Exception e)
            {
                throw new Exception(e.Message, e.InnerException);
            }
            return dtGrid;
        }

        /// <summary>
        /// Method to retrieve the search data from the session
        /// </summary>
        /// <returns></returns>
        private Hashtable GetSearchFromCache()
        {
            Hashtable h = pageCachePolicy.GetCachedObject(Keys.StimulusFundingPageKey) as Hashtable;
            if (h == null || h[SearchCacheKey] == null)
            {
                Hashtable SearchDictionary = new Hashtable();
                return SearchDictionary; 
            }
            else
            {
                return (Hashtable)h[SearchCacheKey];
            }
        }

        /// <summary>
        /// Method to store the search data into the session
        /// </summary>
        /// <param name="hst"></param>
        private void SetSearchFromCache(Hashtable hst)
        {
            Hashtable h = pageCachePolicy.GetCachedObject(Keys.StimulusFundingPageKey) as Hashtable ?? new Hashtable();
            h[SearchCacheKey] = hst;

            pageCachePolicy.Cache(Keys.StimulusFundingPageKey, h);
        }

        /// <summary>
        /// Load the datagrid with sufficient data for display within one page of the data grid. This is done as follows:
        /// Get the summaries for all agreements that the user has access to. Use that to initialize the data grid and
        /// the pager controls based on the page size of the data grid.
        /// The pagers are made visible only when more than one page of data can be displayed.
        /// If there is no data to display, warn the user via the annunciator.
        /// </summary>
        private void LoadDataGrid()
        {
            try
            {
                DataTable dt = new DataTable();
                Hashtable hst = GetSearchFromCache();

                int intStartPageIndex = CurrentPageIndex;
                int intEndingPageIndex = CurrentPageIndex + 1;
                int intPageSize = dgProposal.PageSize;
                string strSort = SortField + " " + SortDir;

                if (GridType == 1)
                {
                    dt = GetProposalData(hst, intStartPageIndex, intEndingPageIndex, intPageSize, strSort);
                }
                else if (GridType == 2)
                {
                    dt = GetSubRecipientData(hst, intStartPageIndex, intEndingPageIndex, intPageSize, strSort);
                }
                else if (GridType == 3)
                {
                    dt = GetVendorData(hst, intStartPageIndex, intEndingPageIndex, intPageSize, strSort);
                }
                                
                pagerBelowDatagrid.RecordCount = pagerAboveDatagrid.RecordCount = (dt.Rows.Count > 0 ? Convert.ToInt32(dt.Rows[0]["RecordCount"].ToString()) : 0);
                int currentPageIndex = CurrentPageIndex;
                int pageCount = (pagerAboveDatagrid.RecordCount / dgProposal.PageSize) +
                    (((pagerAboveDatagrid.RecordCount % dgProposal.PageSize) == 0) ? 0 : 1);
                pagerBelowDatagrid.PageCount = pagerAboveDatagrid.PageCount = pageCount;
                pagerBelowDatagrid.CurrentPage = pagerAboveDatagrid.CurrentPage = currentPageIndex + 1;

                dgProposal.DataSource = dt;
                dgProposal.DataBind();

                if (dt.Rows.Count < 1)
                {
                    downloadBar.Visible = false;
                    dgProposal.Visible = false;
                    pagerAboveDatagrid.Visible = false;
                    pagerBelowDatagrid.Visible = false;
                    annunciator.Message = "No data found for this report.";
                }
                else
                {
                    downloadBar.Visible = true;
                    dgProposal.Visible = true;
                    pagerAboveDatagrid.Visible = true;
                    pagerBelowDatagrid.Visible = true;
                    int startIndex = currentPageIndex * dgProposal.PageSize;
                    pagerBelowDatagrid.FirstRecord = pagerAboveDatagrid.FirstRecord = startIndex + 1;
                    pagerBelowDatagrid.LastRecord = pagerAboveDatagrid.LastRecord = ((startIndex + dgProposal.PageSize) > pagerBelowDatagrid.RecordCount ? pagerBelowDatagrid.RecordCount : startIndex + dgProposal.PageSize);
                }
            }
            catch (Exception ex)
            {
                pagerBelowDatagrid.Visible = pagerAboveDatagrid.Visible = false;
                annunciator.Message = ExceptionReport.GetExceptionInfo(ex, false);
            }
        }
        
        #endregion

        #region DisplayExceptionReport
        /// <summary>
        /// Displays an error message on the screen to the user with the exception, and all
        /// inner exception messages.
        /// </summary>
        /// <param name="ex">The exception to report on</param>
        private void DisplayExceptionReport(Exception ex)
        {
            annunciator.Title = PHSRAG.Insight21.InsightUtilities.Constants.Annunciator.Title;
            annunciator.Message = ExceptionReport.GetExceptionInfo(ex, false);
        }

        #endregion DisplayExceptionReport

        #region Web Form Designer generated code

        /// <summary>
        /// Handler for the event raised when the page is being initialized.
        /// Retrieve the cache policy in force. Deposit event handlers to support the datagrid pagination.
        /// </summary>
        /// <param name="e">Argument associated with the event</param>
        protected override void OnInit(EventArgs e)
        {
            InitializeComponent();
            base.OnInit(e);

            cachePolicy = (Policy.ICachePolicy)Session[Policy.Names.CachePolicyKey];
            pageCachePolicy = (Policy.ICachePolicy)Session[Policy.Names.PageCachePolicyKey];
        }

        /// <summary>
        /// Required method for Designer support - do not modify
        /// the contents of this method with the code editor.
        /// </summary>
        private void InitializeComponent()
        {
        }

        #endregion

        protected void imgBtnReport_Click(object sender, ImageClickEventArgs e)
        {
            try
            {
                string strID = Session["ReportPeriod"].ToString();

                if (CreateXmlReport(DataAccess.GetConnectionString(), strID))
                //if (GenerateXMLReport(DataAccess.GetConnectionString(), strID))
                    annunciator.Message = "All the reports have been generated successfully.";
                
                imgBtnReport.Visible = false; 
            }
            catch (Exception ex)
            {
                annunciator.Message = ExceptionReport.GetExceptionInfo(ex, false);
            }
        }
        
        protected void dgProposal_SortCommand(object source, DataGridSortCommandEventArgs e)
        {
            try
            {
                string SortFld = e.SortExpression.ToString();
                if (GridType == 1)
                {
                    switch (SortFld)
                    {
                        case "First Name":
                            SortFld = "adp.FirstName"; break;
                        case "Last Name":
                            SortFld = "adp.LastName"; break;
                        case "Funding Agency Code":
                            SortFld = "FundingAgency.Code"; break;
                        case "Awarding Agency Code":
                            SortFld = "AwardingAgency.Code"; break;
                        case "ProgramSource TAS":
                            SortFld = "PSource.Code"; break;
                        case "Award Number":
                            SortFld = "AgrFct.SponsorNumber"; break;
                        case "Order Number":
                            SortFld = "Agr.OrderNumber"; break;
                        case "Recipient DUNS Number":
                            SortFld = "Rec.DunsNumber"; break;
                        case "CFDA Number":
                            SortFld = "Agr.CfdaNumber"; break;
                        case "Govt Contract Office Code":
                            SortFld = "GovContract.Code"; break;
                        case "Rec Congressional District":
                            SortFld = "Rec.CongressionalDistrict"; break;
                        case "Recipient Account Number":
                            SortFld = "AgrFct.FundNumber"; break;
                        case "Final Report":
                            SortFld = "QuarStat.IsFinalReport"; break;
                        case "Award Type":
                            SortFld = "Agr.AwardType"; break;
                        case "Award Date":
                            SortFld = "Agr.AwardDate"; break;
                        case "Award Description":
                            SortFld = "AgrFct.Title"; break;
                        case "Program Title":
                            SortFld = "AgrFct.ShortTitle"; break;
                        case "Quarterly Activities":
                            SortFld = "PIQues.QuarterlyActivities"; break;
                        case "Project Status":
                            SortFld = "Agr.ProjectStatus"; break;
                        case "Activity Code":
                            SortFld = "Act.Code"; break;
                        case "Number Of Jobs":
                            SortFld = "PIQues.Id"; break;
                        case "Job Description":
                            SortFld = "PIQues.Narrative"; break;
                        case "Awarded Budget":
                            SortFld = "Agr.AwardedAmount"; break;
                        case "Total ARRA Fund Received":
                            SortFld = "Agr.RevenueAmount"; break;
                        case "Total ARRA Expenditure":
                            SortFld = "Agr.ExpenseAmount"; break;
                        case "Street Address1":
                            SortFld = "Loc.StreetAddress1"; break;
                        case "Street Address2":
                            SortFld = "Loc.StreetAddress2"; break;
                        case "Congressional District":
                            SortFld = "Loc.CongressionalDistrict"; break;
                        case "Is Reporting Applicable":
                            SortFld = "Agr.IsReportingApplicable"; break;
                        case "Highly Compensated Person Name":
                            SortFld = "Person.PersonName"; break;
                        case "Highly Compensated Total Compensation":
                            SortFld = "Person.TotalCompensation"; break;
                        case "Total Number Of SubAwards":
                            SortFld = "Agr.NumberOfSubAwards"; break;
                        case "Total SubAward Amount":
                            SortFld = "Agr.SubAwardAmount"; break;
                        case "Number Of Vendor Payment LessThan 25K":
                            SortFld = "Agr.NumberOfVendorPaymentsLT25K"; break;
                        case "Vendor Payments LessThan 25K":
                            SortFld = "Agr.VendorPaymentsLT25KAmount"; break;
                        case "Number Of SubAwards LessThan 25K":
                            SortFld = "Agr.NumberOfSubAwardsLT25K"; break;
                        case "SubAwards Amount LessThan 25KAmount":
                            SortFld = "Agr.SubAwardsLT25KAmount"; break; 
                    }
                }
                else if (GridType == 2)
                {
                    switch (SortFld)
                    {
                        case "First Name":
                            SortFld = "adp.FirstName"; break;
                        case "Last Name":
                            SortFld = "adp.LastName"; break;                       
                        case "Award Number":
                            SortFld = "AgrFct.SponsorNumber"; break;
                        case "SubRecipient DUNS Number":
                            SortFld = "Rec.DunsNumber"; break;
                        case "Subaward Number":
                            SortFld = "AgrFct.FundNumber"; break;
                        case "SubRecipient Congressional District":
                            SortFld = "Rec.CongressionalDistrict"; break;
                        case "Amount Of Subaward":
                            SortFld = "BudgetAmount"; break;
                        case "Total Subaward Funds Disbursed":
                            SortFld = "SAward.ExpenseAmount"; break;
                        case "Subaward Date":
                            SortFld = "SAward.AwardDate"; break;
                        case "Street Address1":
                            SortFld = "Loc.StreetAddress1"; break;
                        case "Street Address2":
                            SortFld = "Loc.StreetAddress2"; break;
                        case "Arra Primary Performance Location ID":
                            SortFld = "SAward.ArraPrimaryPerformanceLocationID"; break;
                        case "Congressional District":
                            SortFld = "Loc.CongressionalDistrict"; break;
                        case "Is Reporting Applicable":
                            SortFld = "SAward.IsReportingApplicable"; break;
                        case "Highly Compensated Person Name":
                            SortFld = "Person.PersonName"; break;
                        case "Highly Compensated Total Compensation":
                            SortFld = "Person.TotalCompensation"; break;                      
                    }
                }
                else if (GridType == 3)
                {
                    switch (SortFld)
                    {
                        case "First Name":
                            SortFld = "adp.FirstName"; break;
                        case "Last Name":
                            SortFld = "adp.LastName"; break;
                        case "Award Number":
                            SortFld = "AgrFct.SponsorNumber"; break;
                        case "SubAward Number":
                            SortFld = "AgrFct.FundNumber"; break;
                        case "Vendor DUNS Number":
                            SortFld = "SAvendor.DunsNumber"; break;
                        case "HQ Zip Code":
                            SortFld = "SAvendor.HeadquartersZip"; break;
                        case "Vendor Name":
                            SortFld = "SAvendor.[Name]"; break;
                        case "Service Description":
                            SortFld = "SAvendor.ItemDescription"; break;
                        case "Payment Amount":
                            SortFld = "SAvendor.PaymentAmount"; break;
                    }
                }

                SortField = SortFld;
                SortDir = (SortDir == "ASC" ? "DESC" : "ASC");
                LoadDataGrid();
            }
            catch (Exception ex)
            {
                annunciator.Message = ExceptionReport.GetExceptionInfo(ex, false);
            }
        }        

        #region "Create XMl Report programatically"
        /// <summary>
        /// Method to generate the individual XML report for each AwardIdNumber.
        /// </summary>
        /// <param name="connectionString">Connection String</param>
        /// <returns>Boolean</returns>
        public bool CreateXmlReport(string connectionString, string strReportPeriod)
        {
            System.Security.Principal.WindowsImpersonationContext wic = null;
            try
            {
                bool returnBol = true;
                string strAwardIdNumber = string.Empty;
                string strFundNumber = string.Empty;
                string MonthName = String.Format("{0:MMMM}", DateTime.Now).ToString().Substring(0, 3) + DateTime.Now.Day.ToString();
                
                StimulusFunding sf = new StimulusFunding();

                // retrieving the report data for the 3 components
                DataTable dtProposal = sf.GetProposalReportData(connectionString, strReportPeriod);
                DataTable dtSubRecipient = sf.GetSubRecipientReportData(connectionString, strReportPeriod);
                DataTable dtVendor = sf.GetVendorReportData(connectionString, strReportPeriod);

                // Impersonating the user
                wic = Security.Authenticator.ImpersonateUser(
                               Utility.Settings.GetKeyValue("ArraServiceAccount", "RAG29"),
                               Utility.Settings.GetKeyValue("ArraSAPassword", "z0hcq"),
                               Security.Authenticator.LogonType.NewCredentials, Authenticator.LogonProvider.WinNT50);


                // Loop through data for Prime Recipient            
                foreach (DataRow dr in dtProposal.Rows)
                {
                    doc = new XmlDocument();
                    doc.AppendChild(doc.CreateProcessingInstruction("xml", "version='1.0' encoding='UTF-8'"));

                    // XML Header
                    XmlNode root = doc.CreateNode(XmlNodeType.Element, "recoveryrr-GrantLoanReport", null);
                    XmlAttribute rootAttr = doc.CreateAttribute("xsi:schemaLocation");
                    rootAttr.Value = "urn:us:gov:recoveryrr recoveryrr-xc.xsd";
                    root.Attributes.Append(rootAttr);

                    rootAttr = doc.CreateAttribute("xmlns:nc");
                    rootAttr.Value = "http://niem.gov/niem/niem-core/2.0";
                    root.Attributes.Append(rootAttr);

                    rootAttr = doc.CreateAttribute("xmlns:xsi");
                    rootAttr.Value = "http://www.w3.org/2001/XMLSchema-instance";
                    root.Attributes.Append(rootAttr);

                    rootAttr = doc.CreateAttribute("xmlns:recoveryrr");
                    rootAttr.Value = "urn:us:gov:recoveryrr";
                    root.Attributes.Append(rootAttr);

                    rootAttr = doc.CreateAttribute("xmlns:recoveryrr-ext");
                    rootAttr.Value = "urn:us:gov:recoveryrr-ext";
                    root.Attributes.Append(rootAttr);

                    // Grant Loan report Header
                    XmlNode ReportHeader = addNode(doc, root, "recoveryrr-ext-GrantLoanReportHeader", "");  // Addnode
                    addNode(doc, ReportHeader, "recoveryrr-ext-PrimeRecipientDUNS", dr["DunsNumber"].ToString());
                    addNode(doc, ReportHeader, "recoveryrr-ext-AwardIdNumber", dr["AwardNumber"].ToString());
                    addNode(doc, ReportHeader, "recoveryrr-ext-GrantLoanAwardCategory", dr["AwardType"].ToString());

                    // Grant Loan Prime Recipient Report
                    XmlNode PrimeRecipientReport = addNode(doc, root, "recoveryrr-ext-GrantLoanPrimeRecipientReport", "");

                    XmlNode LoanAwardDetail = addNode(doc, PrimeRecipientReport, "recoveryrr-ext-GrantLoanAwardDetail", "");
                    addNode(doc, LoanAwardDetail, "recoveryrr-ext-FundingAgencyCode", dr["FundingAgencyCode"].ToString());
                    addNode(doc, LoanAwardDetail, "recoveryrr-ext-AwardingAgencyCode", dr["AwardingAgencyCode"].ToString());
                    addNode(doc, LoanAwardDetail, "recoveryrr-ext-FinalReportIndicator", (dr["FinalReport"].ToString().Trim() == "False" ? "N" : "Y"));

                    XmlNode CompleteTreasuryAccountSymbol = addNode(doc, LoanAwardDetail, "recoveryrr-ext-CompleteTreasuryAccountSymbol", "");
                    addNode(doc, CompleteTreasuryAccountSymbol, "recoveryrr-ext-TreasuryAccountSymbol", dr["ProgramSourceTAS"].ToString());
                    addNode(doc, CompleteTreasuryAccountSymbol, "recoveryrr-ext-SubAccountCode", "000");

                    addNode(doc, LoanAwardDetail, "recoveryrr-ext-AwardDate", dr["AwardDate"].ToString() == "" ? "1900-01-01" : dr["AwardDate"].ToString());
                    addNode(doc, LoanAwardDetail, "recoveryrr-ext-AwardDescription", dr["AwardDescription"].ToString());
                    addNode(doc, LoanAwardDetail, "recoveryrr-ext-AwardAmount", dr["AwardedBudget"].ToString() == "" ? "0" : dr["AwardedBudget"].ToString());
                    addNode(doc, LoanAwardDetail, "recoveryrr-ext-ProjectDescription", dr["QuarterlyActivities"].ToString() == "" ? "NA" : dr["QuarterlyActivities"].ToString());
                    addNode(doc, LoanAwardDetail, "recoveryrr-ext-ProjectStatus", dr["ProjectStatus"].ToString());
                    addNode(doc, LoanAwardDetail, "recoveryrr-ext-JobCreationNumber", dr["NumberOfJobs"].ToString() == "" ? "0" : dr["NumberOfJobs"].ToString());
                    addNode(doc, LoanAwardDetail, "recoveryrr-ext-JobCreationNarrative", dr["Narrative"].ToString() == "" ? "NA" : dr["Narrative"].ToString());
                    addNode(doc, LoanAwardDetail, "recoveryrr-ext-TotalFederalARRAReceived", dr["RevenueAmount"].ToString() == "" ? "0" : dr["RevenueAmount"].ToString());
                    addNode(doc, LoanAwardDetail, "recoveryrr-ext-TotalNumberSubawardsIndividuals", dr["TotalNumberOfSubAwards"].ToString());
                    addNode(doc, LoanAwardDetail, "recoveryrr-ext-TotalAmountSubawardsIndividuals", dr["TotalSubAwardAmount"].ToString() == "" ? "0" : dr["TotalSubAwardAmount"].ToString());
                    addNode(doc, LoanAwardDetail, "recoveryrr-ext-TotalNumberSmallSubawards", dr["NumberOfSubAwardsLT25K"].ToString());
                    addNode(doc, LoanAwardDetail, "recoveryrr-ext-TotalAmountSmallSubawards", dr["SubAwardsLT25KAmount"].ToString() == "" ? "0" : dr["SubAwardsLT25KAmount"].ToString());
                    addNode(doc, LoanAwardDetail, "recoveryrr-ext-ProjectName", dr["ProgramTitle"].ToString());
                    addNode(doc, LoanAwardDetail, "recoveryrr-ext-CFDAProgramNumber", dr["CFDANumber"].ToString());
                    addNode(doc, LoanAwardDetail, "recoveryrr-ext-GrantLoanActivityCode", dr["ActivityCode"].ToString());
                    addNode(doc, LoanAwardDetail, "recoveryrr-ext-TotalFederalARRAExpenditure", dr["TotalFederalARRAExpenditure"].ToString() == "" ? "0" : dr["TotalFederalARRAExpenditure"].ToString());
                    addNode(doc, LoanAwardDetail, "recoveryrr-ext-TotalFederalARRAInfraExpenditure", "0");
                    addNode(doc, LoanAwardDetail, "recoveryrr-ext-InfrastructureRationale", "");

                    //XmlNode InfrastructureContact = addNode(doc, LoanAwardDetail, "recoveryrr-ext-InfrastructureContact", "");
                    //addNode(doc, InfrastructureContact, "recoveryrr-ext-ContactName", "");

                    //XmlNode InfrastructureAddress = addNode(doc, InfrastructureContact, "recoveryrr-ext-InfrastructureAddress", "");
                    //addNode(doc, InfrastructureAddress, "nc-AddressDeliveryPointText", "");
                    //addNode(doc, InfrastructureAddress, "nc-LocationCityName", "");
                    //addNode(doc, InfrastructureAddress, "recoveryrr-ext-LocationState", "");
                    //addNode(doc, InfrastructureAddress, "nc-LocationPostalCode", "");
                    //addNode(doc, InfrastructureAddress, "nc-LocationPostalExtensionCode", "");

                    //addNode(doc, InfrastructureContact, "nc-ContactEmailID", "");

                    //XmlNode FullTelephoneNumber = addNode(doc, InfrastructureContact, "nc-FullTelephoneNumber", "");
                    //addNode(doc, FullTelephoneNumber, "nc-TelephoneNumberFullID", "");
                    //addNode(doc, FullTelephoneNumber, "nc-TelephoneSuffixID", "");

                    addNode(doc, LoanAwardDetail, "recoveryrr-ext-TotalNumberVendorPayments", dr["NoOfVendorPaymentLT25K"].ToString());
                    addNode(doc, LoanAwardDetail, "recoveryrr-ext-TotalAmountVendorPayments", dr["VendorPaymentsLT25K"].ToString() == "" ? "0" : dr["VendorPaymentsLT25K"].ToString());

                    XmlNode PrimeRecipient = addNode(doc, PrimeRecipientReport, "recoveryrr-ext-PrimeRecipient", "");
                    string strDis = string.Empty;
                    if (dr["RecCongressionalDistrict"].ToString() == "DC" || dr["RecCongressionalDistrict"].ToString() == "")
                        strDis = "00";
                    else
                        strDis = dr["RecCongressionalDistrict"].ToString();
                    addNode(doc, PrimeRecipient, "recoveryrr-ext-CongressionalDistrict", strDis);
                    addNode(doc, PrimeRecipient, "recoveryrr-ext-AccountNumber", dr["RecipientAccountNumber"].ToString());
                    addNode(doc, PrimeRecipient, "recoveryrr-ext-CompensatedOfficerIndicator", "No");

                    XmlNode CompensatedOfficer = null;
                    if (dr["PersonName"].ToString().Trim() != "")
                    {
                        CompensatedOfficer = addNode(doc, PrimeRecipient, "recoveryrr-ext-CompensatedOfficer", "");
                        addNode(doc, CompensatedOfficer, "nc-PersonFullName", dr["PersonName"].ToString());
                        addNode(doc, CompensatedOfficer, "recoveryrr-ext-OfficerCompensation", dr["TotalCompensation"].ToString() == "" ? "0" : dr["TotalCompensation"].ToString());
                    }

                    XmlNode PlaceOfPerformance = addNode(doc, PrimeRecipientReport, "recoveryrr-ext-PlaceOfPerformance", "");

                    XmlNode Address = addNode(doc, PlaceOfPerformance, "recoveryrr-ext-Address", "");
                    addNode(doc, Address, "nc-AddressDeliveryPointText", dr["StreetAddress1"].ToString() + " " + dr["StreetAddress2"].ToString());
                    addNode(doc, Address, "nc-LocationCityName", dr["City"].ToString() == "" ? "NA" : dr["City"].ToString());
                    addNode(doc, Address, "recoveryrr-ext-LocationState", dr["State"].ToString() == "" ? "NA" : dr["State"].ToString());
                    addNode(doc, Address, "recoveryrr-ext-LocationCountry", dr["Country"].ToString() == "" ? "NA" : dr["Country"].ToString());
                    addNode(doc, Address, "nc-LocationPostalCode", dr["Zip"].ToString().Length == 9 ? dr["Zip"].ToString().Substring(0, 5) : dr["Zip"].ToString());
                    addNode(doc, Address, "nc-LocationPostalExtensionCode", dr["Zip"].ToString().Length == 9 ? dr["Zip"].ToString().Substring(5) : "0000");

                    if (dr["CongressionalDistrict"].ToString() == "DC" || dr["CongressionalDistrict"].ToString() == "")
                        strDis = "00";
                    else
                        strDis = dr["CongressionalDistrict"].ToString();
                    addNode(doc, PlaceOfPerformance, "recoveryrr-ext-CongressionalDistrict", strDis);

                    // storing the Fund Number and AwardIdNumber.
                    strFundNumber = dr["RecipientAccountNumber"].ToString();
                    strAwardIdNumber = dr["AwardNumber"].ToString().ToLower().Trim();

                    // Loop through data for Sub Recipient  
                    foreach (DataRow dr1 in dtSubRecipient.Rows)
                    {
                        string subRecAwardNumber = dr1["AwardNumber"].ToString().ToLower().Trim();
                        if (subRecAwardNumber == strAwardIdNumber)
                        {
                            // Grant Loan Sub Recipient Report
                            XmlNode SubRecipientReport = addNode(doc, root, "recoveryrr-ext-GrantLoanSubRecipientReport", "");
                            addNode(doc, SubRecipientReport, "recoveryrr-ext-SubAwardNumber", dr1["SubawardNumber"].ToString());
                            addNode(doc, SubRecipientReport, "recoveryrr-ext-AwardAmount", dr1["AmountOfSubaward"].ToString() == "" ? "0" : dr1["AmountOfSubaward"].ToString());
                            addNode(doc, SubRecipientReport, "recoveryrr-ext-AwardDate", dr1["SubawardDate"].ToString());

                            XmlNode SubRecipient = addNode(doc, SubRecipientReport, "recoveryrr-ext-SubRecipient", "");
                            addNode(doc, SubRecipient, "recoveryrr-ext-SubRecipientDUNS", dr1["SubRecipientDUNSNumber"].ToString());

                            if (dr1["SubRecipientCongressionalDistrict"].ToString() == "DC" || dr1["SubRecipientCongressionalDistrict"].ToString() == "")
                                strDis = "00";
                            else
                                strDis = dr1["SubRecipientCongressionalDistrict"].ToString();
                            addNode(doc, SubRecipient, "recoveryrr-ext-CongressionalDistrict", strDis);
                            if (dr1["SubawardNumber"].ToString() == "214846")
                            {
                                addNode(doc, SubRecipient, "recoveryrr-ext-CompensatedOfficerIndicator", dr1["CompensatedOfficerIndicator"].ToString());
                            }
                            else
                            {
                                addNode(doc, SubRecipient, "recoveryrr-ext-CompensatedOfficerIndicator", "No");
                            }

                            if (dr1["PersonName"].ToString().Trim() != "")
                            {
                                CompensatedOfficer = addNode(doc, SubRecipient, "recoveryrr-ext-CompensatedOfficer", "");
                                addNode(doc, CompensatedOfficer, "nc-PersonFullName", dr1["PersonName"].ToString());
                                addNode(doc, CompensatedOfficer, "recoveryrr-ext-OfficerCompensation", dr1["TotalCompensation"].ToString() == "" ? "0" : dr1["TotalCompensation"].ToString());
                            }

                            PlaceOfPerformance = addNode(doc, SubRecipientReport, "recoveryrr-ext-PlaceOfPerformance", "");

                            Address = addNode(doc, PlaceOfPerformance, "recoveryrr-ext-Address", "");
                            addNode(doc, Address, "nc-AddressDeliveryPointText", dr1["StreetAddress1"].ToString() + " " + dr1["StreetAddress2"].ToString());
                            addNode(doc, Address, "nc-LocationCityName", dr1["City"].ToString() == "" ? "NA" : dr1["City"].ToString());
                            addNode(doc, Address, "recoveryrr-ext-LocationState", dr1["State"].ToString() == "" ? "NA" : dr1["State"].ToString());
                            addNode(doc, Address, "recoveryrr-ext-LocationCountry", dr1["Country"].ToString() == "" ? "NA" : dr1["Country"].ToString());
                            addNode(doc, Address, "nc-LocationPostalCode", dr1["Zip"].ToString().Length == 9 ? dr1["Zip"].ToString().Substring(0, 5) : dr1["Zip"].ToString());
                            addNode(doc, Address, "nc-LocationPostalExtensionCode", dr1["Zip"].ToString().Length == 9 ? dr1["Zip"].ToString().Substring(5) : "0000");

                            if (dr1["CongressionalDistrict"].ToString() == "DC" || dr1["CongressionalDistrict"].ToString() == "")
                                strDis = "00";
                            else
                                strDis = dr1["CongressionalDistrict"].ToString();
                            addNode(doc, PlaceOfPerformance, "recoveryrr-ext-CongressionalDistrict", strDis);
                            addNode(doc, SubRecipientReport, "recoveryrr-ext-SubAwardAmountDisbursed", dr1["TotalSubawardFundsDisbursed"].ToString() == "" ? "0" : dr1["TotalSubawardFundsDisbursed"].ToString());
                        }
                    }

                    // Loop through data for Vendor  
                    foreach (DataRow dr2 in dtVendor.Rows)
                    {
                        string vendorAwardNumber = dr2["AwardNumber"].ToString().ToLower().Trim();
                        if (vendorAwardNumber == strAwardIdNumber)
                        {
                            // Vendor Report
                            XmlNode VendorReport = addNode(doc, root, "recoveryrr-ext-Vendor", "");
                            addNode(doc, VendorReport, "recoveryrr-ext-AwardIdNumber", dr2["AwardNumber"].ToString());
                            addNode(doc, VendorReport, "recoveryrr-ext-SubAwardNumber", dr2["SubawardNumber"].ToString());
                            addNode(doc, VendorReport, "nc-OrganizationName", dr2["Name"].ToString());
                            addNode(doc, VendorReport, "recoveryrr-ext-VendorDUNS", dr2["DunsNumber"].ToString());
                            addNode(doc, VendorReport, "nc-LocationPostalCode", dr2["HeadquartersZip"].ToString());
                            addNode(doc, VendorReport, "nc-LocationPostalExtensionCode", "");
                            addNode(doc, VendorReport, "recoveryrr-ext-ProductServiceDescription", dr2["ItemDescription"].ToString());
                            addNode(doc, VendorReport, "recoveryrr-ext-PaymentAmount", dr2["PaymentAmount"].ToString());
                        }
                    }

                    //  Replacing some xml nodes
                    doc.AppendChild(root);
                    string sXml = doc.InnerXml;
                    sXml = sXml.Replace("schemaLocation", "xsi:schemaLocation");
                    sXml = sXml.Replace("recoveryrr-ext-", "recoveryrr-ext:");
                    sXml = sXml.Replace("recoveryrr-GrantLoanReport", "recoveryrr:GrantLoanReport");
                    sXml = sXml.Replace("nc-", "nc:");


                    // Save XML in the given path
                    string xmlPath = Utility.Settings.GetKeyValue("ArraReportSharedLocation", @"\\phsweb418\arra$\");
                    string fileName = xmlPath + MonthName + strFundNumber + ".xml";                    
                    SaveXML(fileName, sXml);
                }

                // turn impersonation off
                Security.Authenticator.RevertUser(wic);

                // Freeze the Report data for the current period for future updation.
                sf.FreezeReportData(connectionString, strReportPeriod);

                //return the completion status
                return returnBol;
            }
            catch (Exception ex)
            {
                if (wic != null)
                    Security.Authenticator.RevertUser(wic);
                Utility.ExceptionReport.WriteToEventLog("Failed to generate xml reports ,Error occurred while executing the StimulusFunding.CreateXmlReport()", ex, true, Utility.ExceptionReport.ReportType.Error);
                throw;
            }
        }

        /// <summary>
        /// Method to add a Xml node. 
        /// </summary>
        /// <param name="doc">Xml Document</param>
        /// <param name="root">Root Node</param>
        /// <param name="name">Element Name</param>
        /// <param name="val">Value</param>
        /// <returns></returns>
        private XmlNode addNode(XmlDocument doc, XmlNode root, string name, string val)
        {
            XmlNode node = doc.CreateNode(XmlNodeType.Element, name, null);
            node.InnerText = val;
            root.AppendChild(node);
            return node;
        }

        /// <summary>
        /// Method to add an Xml attribute to a node.
        /// </summary>
        /// <param name="doc">Xml Document</param>
        /// <param name="root">Root Node</param>
        /// <param name="name">Element Name</param>
        /// <param name="val">Value</param>
        private void addAttribute(XmlDocument doc, XmlNode root, string name, string val)
        {
            XmlAttribute attribute = doc.CreateAttribute(name);
            attribute.InnerText = val;
            root.Attributes.Append(attribute);
        }

        /// <summary>
        /// Method to save the XML string to a XML file.
        /// </summary>
        /// <param name="FileName">XML file name</param>
        /// <param name="sXml">XML string</param>
        private void SaveXML(string FileName, string sXml)
        {
            StreamWriter objStreamWriter = File.CreateText(FileName);
            //File.Move(outputFile, stagedOutputFile);
            objStreamWriter.Write(sXml);
            objStreamWriter.Close();
        }

        #endregion

        protected void btnAdvSearch_Click(object sender, EventArgs e)
        {
            
        }
        
}
}